* collapse our quarterly worker data to annual;

* [] brackets indicate redacted internal census variable name or directory that can't be disclosed. ;

* define libraries;
libname icf '[directory location for geocoded worker residences]';
libname hannah  '/projects/users/########';
libname ben '/projects/users/########';

proc sql;
  create table hannah.ehf_icf_states as
  select pik, year,
  max([annual earnings]) as [annual earnings],
  max([county id]) as [county id],
  mean(age) as age,
  mean(y) as y,
  mean(x) as x,
  mean(unemp) as unemp,
  max(female) as female, max(bornusa) as bornusa, max(hispanic) as hispanic,
  max(white) as white, max(black) as black, max(ind_nat) as ind_nat,
  max(asian) as asian, max(haw_pi) as haw_pi, max(more_race) as more_race,
  max(nohigh) as nohigh, max(highsch) as highsch, max(somecoll) as somecoll,
  max(college) as college
  from hannah.ehf_icf_states
  group by pik, year
  order by pik, year;
quit;
